/*
 * Author: Minh-Tuan Ta
 * Date: 11/6/11
 * Class: HelfulRatingDB
 * This class used to access the data from HelpfulRating table
 */
package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.*;
import beans.*;

public class HelpFulRatingDB extends DB {
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/7/11
	 * Method: getHelpfulRatings
	 * This method will get all the helpful rating in the database
	 */
	public static synchronized ArrayList<HelpfulRating> getRatings()
	{
		Connection conn = null;
		ArrayList<HelpfulRating> ratings = new ArrayList<HelpfulRating>();
		PreparedStatement statement = null;
		String sql = "SELECT contentid, userid, rateValue, ratingdate\n" +
					 "FROM helpfulrating";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			
			while (rs.next())
			{
				HelpfulRating rating = new HelpfulRating();
				setHelpfulRating(rating, rs);
				
				ratings.add(rating);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return ratings;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * 		 : COREY STONE
	 * Date: 11/7/11
	 * Method: getHelpfulRatings
	 * This method will get all the helpful rating for a content using contentid in the database
	 */
	public static synchronized ArrayList<HelpfulRating> getHelpfulRatingsOfContent(int contentid)
	{
		Connection conn = null;
		ArrayList<HelpfulRating> ratings = new ArrayList<HelpfulRating>();
		PreparedStatement statement = null;
		String sql = "SELECT contentid, userid, rateValue, ratingdate\n" +
					 "FROM helpfulrating\n" +
					 "WHERE contentid = ? AND rateValue = 1";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, contentid);
			ResultSet rs = statement.executeQuery();
			
			while (rs.next())
			{
				HelpfulRating rating = new HelpfulRating();
				setHelpfulRating(rating, rs);
				
				ratings.add(rating);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return ratings;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * 	     : COREY STONE
	 * Date: 11/7/11
	 * Method: getHelpfulRatings
	 * This method will get all the helpful rating for a content using contentid in the database
	 */
	public static synchronized ArrayList<HelpfulRating> getNotHelpfulRatingsOfContent(int contentid)
	{
		Connection conn = null;
		ArrayList<HelpfulRating> ratings = new ArrayList<HelpfulRating>();
		PreparedStatement statement = null;
		String sql = "SELECT contentid, userid, rateValue, ratingdate\n" +
					 "FROM helpfulrating\n" +
					 "WHERE contentid = ? AND rateValue = 0";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, contentid);
			ResultSet rs = statement.executeQuery();
			
			while (rs.next())
			{
				HelpfulRating rating = new HelpfulRating();
				setHelpfulRating(rating, rs);
				
				ratings.add(rating);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return ratings;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * 	     : COREY STONE
	 * Date: 11/7/11
	 * Method: getHelpfulRatings
	 * This method will get all the helpful rating of a user using userid in the database
	 */
	public static synchronized ArrayList<HelpfulRating> getRatingsOfUser(int userid)
	{
		Connection conn = null;
		ArrayList<HelpfulRating> ratings = new ArrayList<HelpfulRating>();
		PreparedStatement statement = null;
		String sql = "SELECT contentid, userid, rateValue, ratingdate\n" +
					 "FROM helpfulrating\n" +
					 "WHERE userid = ?";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, userid);
			ResultSet rs = statement.executeQuery();
			
			while (rs.next())
			{
				HelpfulRating rating = new HelpfulRating();
				setHelpfulRating(rating, rs);
				
				ratings.add(rating);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return ratings;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Author: COREY STONE
	 * Date: 11/6/11
	 * Method: addHelpfulRating
	 * This method will add a HelpfulRating for a content
	 */
	public static synchronized int addRating(HelpfulRating helpfulrating)
	{
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "INSERT INTO helpfulrating(contentid, userid, rateValue, ratingdate)\n" +
					 "VALUES(?, ?, ?, ?)";

		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, helpfulrating.getContentID());
			statement.setInt(2, helpfulrating.getUserID());
			statement.setInt(3, helpfulrating.getRateValue());
			statement.setDate(4, helpfulrating.getRatingDate());
			
			status = statement.executeUpdate();
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * 	     : COREY STONE
	 * Date: 11/6/11
	 * Method: updateHelpfulRating
	 * This method will update a rating for a content
	 */
	public static synchronized int updateRating(HelpfulRating helpfulrating)
	{
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "UPDATE helpfulrating\n" +
					 "SET rateValue = ?, ratingdate = ?\n" +
					 "WHERE contentid = ? AND userid = ?";

		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(3, helpfulrating.getContentID());
			statement.setInt(4, helpfulrating.getUserID());
			statement.setInt(1, helpfulrating.getRateValue());
			statement.setDate(2, helpfulrating.getRatingDate());
			
			status = statement.executeUpdate();
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * 		 : COREY STONE
	 * Date: 11/6/11
	 * Method: deleteHelpfulRating
	 * This method will delete a rating for a content. Only use it administrative works.
	 */
	public static synchronized int deleteRating(HelpfulRating helpfulrating)
	{
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "DELETE FROM helpfulrating\n" +
					 "WHERE contentid = ? AND userid = ?";

		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, helpfulrating.getContentID());
			statement.setInt(2, helpfulrating.getUserID());
			
			status = statement.executeUpdate();
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;
	}
	
	public static synchronized boolean isRated(int contentID, int userID)
	{
		boolean r = false;
		Connection conn = null;
		PreparedStatement statement = null;
		String sql = "SELECT rateValue\n" +
					 "FROM helpfulrating\n" +
					 "WHERE contentid = ? AND userid = ?";

		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, contentID);
			statement.setInt(2, userID);
			
			ResultSet rs = statement.executeQuery();
			
			if (rs.next())
			{
				r = true;
			}
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return r;
	}
}
